package org.nebuloop.photo.organizer.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.HelpFormatter;
import org.apache.commons.cli.Options;
import org.apache.commons.cli.PosixParser;
import org.nebuloop.photo.organizer.dao.DBSetup.DATABASE_TYPE;

/**
 * 
 */
public class DataMigrator {

  private static final Logger LOGGER = Logger.getLogger(DataMigrator.class.getName());

  private static String JDBC_DRIVER;
  private static String JDBC_URL;
  private static String JDBC_USER;
  private static String JDBC_PW;
  private static DATABASE_TYPE DB_TYPE = DATABASE_TYPE.MYSQL;

  public static void main(String... args) {
    try {
      Options options = new Options();
      options.addOption("i", true, "Input Directory");
      CommandLineParser parser = new PosixParser();
      CommandLine cmd = parser.parse(options, args);

      if (!cmd.hasOption("i")) {
        LOGGER.severe("Required parameters <input> not specified");
        HelpFormatter helpFormatter = new HelpFormatter();
        helpFormatter.printHelp("java " + DataMigrator.class.getName(), options);
        System.exit(-1);
      }
      String inputDir = cmd.getOptionValue("i");
      LOGGER.log(Level.INFO, "Input Path : {0}", inputDir);

      // create source connection
      Class.forName("org.hsqldb.jdbcDriver");
      Class.forName("com.mysql.jdbc.Driver");
      String srcDb = String.format("jjdbc:hsqldb:file:%s/imagedb", inputDir);
      try (Connection srcConn = DriverManager.getConnection(srcDb, "", "");
          Connection destConn = DriverManager.getConnection("jdbc:mysql://192.168.1.21:3306/imagedb", "imgadm",
              "imgadm");) {

        destConn.setAutoCommit(true);
        setupDB(destConn);

        {
          long m = 0;
          Statement stmt = srcConn.createStatement();
          ResultSet rs = stmt.executeQuery("select ID, NAME, DEST_PATH, MD5, CREATE_TIME, IMPORTED_TIME, HAS_EXIF "
              + "from IMAGE_INFO");
          PreparedStatement pstmt = destConn
              .prepareStatement("insert into IMAGE_INFO (ID, NAME, DEST_PATH, MD5, CREATE_TIME, IMPORTED_TIME, HAS_EXIF) "
                  + "values (?, ?, ?, ?, ?, ?, ?) ");
          while (rs.next()) {
            pstmt.clearParameters();
            pstmt.setInt(1, rs.getInt("ID"));
            pstmt.setString(2, rs.getString("NAME"));
            pstmt.setString(3, rs.getString("DEST_PATH"));
            pstmt.setString(4, rs.getString("MD5"));
            pstmt.setTimestamp(5, rs.getTimestamp("CREATE_TIME"));
            pstmt.setTimestamp(6, rs.getTimestamp("IMPORTED_TIME"));
            pstmt.setBoolean(7, rs.getBoolean("HAS_EXIF"));

            pstmt.executeUpdate();
            m++;
          }
          rs.close();
          pstmt.close();
          stmt.close();
          LOGGER.log(Level.INFO, "Migrated {0} records in IMAGE_INFO", m);
        }
        {
          long m = 0;
          Statement stmt = srcConn.createStatement();
          ResultSet rs = stmt.executeQuery("select ID, IMG_ID, SRC_PATH, SRC_ID, CHECKED_TIME " + "from SRC_INFO");
          PreparedStatement pstmt = destConn
              .prepareStatement("insert into SRC_INFO (ID, IMG_ID, SRC_PATH, SRC_ID, CHECKED_TIME) "
                  + "values (?, ?, ?, ?, ?) ");
          while (rs.next()) {
            pstmt.clearParameters();
            pstmt.setInt(1, rs.getInt("ID"));
            pstmt.setInt(2, rs.getInt("IMG_ID"));
            pstmt.setString(3, rs.getString("SRC_PATH"));
            pstmt.setString(4, rs.getString("SRC_ID"));
            pstmt.setTimestamp(5, rs.getTimestamp("CHECKED_TIME"));

            pstmt.executeUpdate();
            m++;
          }
          rs.close();
          pstmt.close();
          stmt.close();
          LOGGER.log(Level.INFO, "Migrated {0} records in SRC_INFO", m);
        }
      }

    } catch (Exception ex) {
      LOGGER.log(Level.WARNING, "Error ", ex);
    }
  }

  private synchronized static void setupDB(Connection connection) throws SQLException {
    Statement stmt = null;
    try {
      stmt = connection.createStatement();
      String[] strDdls;
      switch (DB_TYPE) {
      case HSQLDB: {
        strDdls = new String[] {
            "create table IMAGE_INFO (ID IDENTITY, " + "NAME VARCHAR(255) NOT NULL, DEST_PATH VARCHAR(255), "
                + "MD5 VARCHAR(50) NOT NULL, CREATE_TIME DATETIME, " + "IMPORTED_TIME DATETIME, HAS_EXIF BOOLEAN) " // +
                // "CONSTRAINT cnts1 UNIQUE (MD5)"
            ,
            "create table SRC_INFO (ID IDENTITY, IMG_ID INTEGER NOT NULL, SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME DATETIME)" };
        break;
      }
      case DERBY: {
        strDdls = new String[] {
            "create table IMAGE_INFO (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "NAME VARCHAR(255) NOT NULL, DEST_PATH VARCHAR(255), "
                + "MD5 VARCHAR(50) NOT NULL, CREATE_TIME TIMESTAMP, " + "IMPORTED_TIME TIMESTAMP, HAS_EXIF SMALLINT) " // +
                // "CONSTRAINT cnts1 UNIQUE (MD5)"
            ,
            "create table SRC_INFO (ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), "
                + "IMG_ID INTEGER NOT NULL, SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME TIMESTAMP)" };
        break;
      }
      case MYSQL: {
        strDdls = new String[] {
            "CREATE TABLE IMAGE_INFO (ID INT  NOT NULL PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(255) NOT NULL, "
                + " DEST_PATH VARCHAR(500)  NOT NULL, MD5 VARCHAR(50)  NOT NULL, CREATE_TIME TIMESTAMP  NOT NULL, "
                + " IMPORTED_TIME TIMESTAMP  NOT NULL, HAS_EXIF BOOLEAN) ENGINE = MyISAM",
            "ALTER TABLE IMAGE_INFO ADD INDEX idx_md5(MD5)",
            "create table SRC_INFO (ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT, IMG_ID INT NOT NULL,"
                + " SRC_PATH VARCHAR(255), SRC_ID VARCHAR(100), CHECKED_TIME TIMESTAMP)",
            "ALTER TABLE SRC_INFO ADD INDEX idx_imgid(IMG_ID)",
            "select name, (select count(b.ID) from SRC_INFO b where b.IMG_ID = i.ID) as cnt, DEST_PATH, SRC_PATH, SRC_ID from IMAGE_INFO i join SRC_INFO s on i.ID = s.IMG_ID where (select count(b.ID) from SRC_INFO b where b.IMG_ID = i.ID) > 1" };
        break;
      }
      default: {
        strDdls = new String[0];
      }
      }
      for (String strDdl : strDdls) {
        runDdl(strDdl, stmt);
      }
    } catch (SQLException ex) {
      LOGGER.log(Level.WARNING, "Error while creating tables. Ignore if it is related to table already exist", ex);
    } finally {
      try {
        if (stmt != null) {
          stmt.close();
        }
      } catch (SQLException e) {
        LOGGER.log(Level.WARNING, "Error in closing statement", e);
      }
    }
  }

  private static synchronized boolean runDdl(String strDdl, Statement stmt) throws SQLException {
    LOGGER.log(Level.INFO, "Running DDL : {0}", strDdl);
    boolean a = stmt.execute(strDdl);
    LOGGER.log(Level.INFO, "First create table return : {0}", a);
    return a;
  }
}
